package com.vegetable.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.exceptions.ExceptionUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Map;
import java.util.Objects;

/**
 * 功能描述:
 * 〈Excel 导入导出工具类〉
 * @author : ZhangYang
 * @date : 2019/11/20 17:38
 */
@Slf4j
public class ExcelUtils {


    /**
     * 〈根据模板生成Excel，并直接返回前端供用户下载〉
     * @param templateUrl   模板URL
     * @param data          模板数据
     * @param fileName      用户下载的文件名称（不带拓展名）
     * @author ZhangYang
     * @date 2020/12/30 10:32
     * @version 1.0.0
     */
    public static void downLoadExcelByTemplate(String templateUrl,Map<String,Object> data, String fileName){
        try {
            TemplateExportParams tep = new TemplateExportParams(templateUrl,true);
            Workbook workbook = ExcelExportUtil.exportExcel(tep, data);
            //下载文件
            downLoadExcel(fileName,workbook);
        }catch (Exception e){
            log.error(ExceptionUtil.getMessage(e));
        }
    }

    /**
     * 〈将文件返回给前端客户〉
     * @param fileName 文件名称
     * @param workbook excel数据
     * @author ZhangYang
     * @date 2020/12/30 10:36
     * @version 1.0.0
     */
    public static void downLoadExcel(String fileName, Workbook workbook) throws IOException {
        try {
            HttpServletResponse response = HttpServletUtils.getResponse();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }finally {
            workbook.close(); // 关闭工作簿
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private final String value;
        ExcelTypeEnum(String value) {
            this.value = value;
        }
        public String getValue() {
            return value;
        }
    }
    public static CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 深蓝色背景
        style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 白色加粗字体
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("微软雅黑");
        style.setFont(font);

        // 设置边框
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);
        style.setBottomBorderColor(IndexedColors.DARK_BLUE.getIndex());
        style.setTopBorderColor(IndexedColors.DARK_BLUE.getIndex());
        style.setLeftBorderColor(IndexedColors.DARK_BLUE.getIndex());
        style.setRightBorderColor(IndexedColors.DARK_BLUE.getIndex());

        // 居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }
    // 创建表头样式
    public static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
//        style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        // 设置底部边框样式为粗实线，这里体现宽度变化，比细实线更粗
        style.setBorderBottom(BorderStyle.THIN);
        // 设置左边框样式为双实线，宽度也由对应的样式决定，双实线相对较粗
        style.setBorderLeft(BorderStyle.THIN);
        // 设置右边框样式为点划线，有其特定宽度表现
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    // 创建行样式
    public static CellStyle createRowStyle(Workbook workbook, IndexedColors color) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(color.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        // 设置底部边框样式为粗实线，这里体现宽度变化，比细实线更粗
        style.setBorderBottom(BorderStyle.THIN);
        // 设置左边框样式为双实线，宽度也由对应的样式决定，双实线相对较粗
        style.setBorderLeft(BorderStyle.THIN);
        // 设置右边框样式为点划线，有其特定宽度表现
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    public static String getStringCell(Row currentR, int index){
        String result = "";
        Cell tCell = currentR.getCell(index);
        if(Objects.isNull(tCell)){
            return result;
        }
        switch (tCell.getCellType()){
            case NUMERIC:
                result = tCell.getNumericCellValue() + "";
                break;
            case STRING:
                result = tCell.getStringCellValue();
                break;
        }
        return result.trim();
    }

    public static BigDecimal getBCCell(Row currentR, int index){
        BigDecimal result = BigDecimal.ZERO;
        Cell tCell = currentR.getCell(index);
        if(Objects.isNull(tCell)){
            return result;
        }
        switch (tCell.getCellType()){
            case NUMERIC:
                result = BigDecimal.valueOf(tCell.getNumericCellValue());
                break;
            case STRING:
                result = new BigDecimal(tCell.getStringCellValue().replace("\t", "").replace("-", "").replace("+", "").replace(",", ""));
                break;
        }
        return result;
    }

    public static Double getDoubleCell(Row currentR, int index){
        double result = 0d;
        Cell tCell = currentR.getCell(index);
        if(Objects.isNull(tCell)){
            return result;
        }
        switch (tCell.getCellType()){
            case NUMERIC:
                result = tCell.getNumericCellValue();
                break;
            case STRING:
                result = Double.parseDouble(tCell.getStringCellValue());
                break;
        }
        return result;
    }
}
